﻿
/*
 * Redesign the table schema for UDS_Flow_Jump
 */
-- Remove constraints
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = object_id(N'dbo.[PK_UDS_Flow_Jump]') AND parent_object_id = object_id(N'dbo.UDS_Flow_Jump'))
    ALTER TABLE [dbo].[UDS_Flow_Jump] DROP CONSTRAINT [PK_UDS_Flow_Jump];
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = object_id(N'dbo.[FK_UDS_Flow_Jump_FlowStepID]') AND parent_object_id = object_id(N'dbo.UDS_Flow_Jump'))
    ALTER TABLE [dbo].[UDS_Flow_Jump] DROP CONSTRAINT [FK_UDS_Flow_Jump_FlowStepID];
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = object_id(N'dbo.[CK_UDS_Flow_Jump_Priority]') AND parent_object_id = object_id(N'dbo.UDS_Flow_Jump'))
    ALTER TABLE [dbo].[UDS_Flow_Jump] DROP CONSTRAINT [CK_UDS_Flow_Jump_Priority];
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = object_id(N'dbo.[CK_UDS_Flow_Jump_ToStepID]') AND parent_object_id = object_id(N'dbo.UDS_Flow_Jump'))
    ALTER TABLE [dbo].[UDS_Flow_Jump] DROP CONSTRAINT [CK_UDS_Flow_Jump_ToStepID];
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = object_id(N'dbo.[CK_UDS_Flow_Jump_Compare]') AND parent_object_id = object_id(N'dbo.UDS_Flow_Jump'))
    ALTER TABLE [dbo].[UDS_Flow_Jump] DROP CONSTRAINT [CK_UDS_Flow_Jump_Compare];
GO


CREATE TABLE dbo.Tmp_UDS_Flow_Jump
(
	Flow_ID         int NOT NULL,
	Step_ID         int NOT NULL,
	Priority        int NOT NULL,
	FieldName       nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	To_Step_ID      int NOT NULL,
	Flow_Rule       int NOT NULL,
	Compare         nvarchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
	CompareValue    numeric(18, 0) NOT NULL,

    CONSTRAINT [PK_UDS_Flow_Jump] PRIMARY KEY CLUSTERED (Flow_ID, Step_ID, Priority),
    CONSTRAINT [FK_UDS_Flow_Jump_FlowStepID] FOREIGN KEY (Flow_ID, Step_ID) REFERENCES dbo.UDS_Flow_Step(Flow_ID, Step_ID) ON DELETE CASCADE,
	CONSTRAINT [CK_UDS_Flow_Jump_Priority] CHECK ((Priority > 0) OR (Priority = -1)),
	CONSTRAINT [CK_UDS_Flow_Jump_ToStepID] CHECK (To_Step_ID >= 0),
	CONSTRAINT [CK_UDS_Flow_Jump_Compare] CHECK (Compare in ('>', '=', '<', '>=', '<=', '<>', '!='))
)
GO

-- Add an identity column to UDS_Flow_Jump table for removing duplate records
ALTER TABLE dbo.UDS_Flow_Jump ADD [IdentityID] int NOT NULL IDENTITY(1,1);
GO

-- Migrate data to table Tmp_UDS_Flow_Jump
IF EXISTS(SELECT 1 FROM dbo.UDS_Flow_Jump)
BEGIN
    DECLARE @tmp TABLE ( IdentityID int NOT NULL );
    INSERT INTO @tmp (IdentityID)
        SELECT MAX(IdentityID)
        FROM dbo.UDS_Flow_Jump
        GROUP BY Flow_ID, Step_ID, Priority

    INSERT INTO dbo.Tmp_UDS_Flow_Jump (FieldName, Compare, CompareValue, Flow_ID, Step_ID, Priority, To_Step_ID, Flow_Rule)
        SELECT j.FieldName, j.Compare, j.CompareValue, j.Flow_ID, j.Step_ID, j.Priority, j.To_Step_ID, j.Flow_Rule
        FROM dbo.UDS_Flow_Jump j INNER JOIN @tmp t ON j.IdentityID = t.IdentityID
END
GO


DROP TABLE dbo.UDS_Flow_Jump;
GO


exec  sys.sp_rename @objname = N'dbo.Tmp_UDS_Flow_Jump',  @newname = N'UDS_Flow_Jump', @objtype = 'OBJECT';
GO
